import math

from odoo import fields, models, api


class MaterialRequirementsAnalysisReport(models.AbstractModel):
    _name = 's.material.requirements.analysis.report'
    _description = 'Material Requirements Analysis'
    # _auto = False

    @api.model
    def get_data(self):
        sale_line_ids = self.env['sale.order.line'].search([
            ('product_id.detailed_type', '=', 'product'),
            ('order_id.state', '=', 'sale'),
            ('s_remaining_planned_qty', '>', 0),
            ('s_plan_close', '=', False)
        ])
        sale_line_vals_list = []
        bom_vals = {}
        material = {}
        for line in sale_line_ids:

            value_pnl = line.product_id.value_pnl or 1
            value_sheet = line.product_id.s_value_sheet or 1
            pcs_total = value_pnl * value_sheet or 1
            expect_planned_qty = math.ceil(line.s_remaining_planned_qty / pcs_total) * pcs_total
            sale_line_vals_list.append({
                'id': line.id,
                'sale_name': line.order_id.name,
                'product_id': line.product_id.id,
                'value_pnl': value_pnl,
                'value_sheet': value_sheet,
                'product_name': line.product_id.name or '',
                'product_qty': line.product_uom_qty,
                'uom_id': line.product_uom.id,
                'uom_name': line.product_uom.name,
                'expect_planned_qty': expect_planned_qty,
                'expect_planned_pnl_qty': expect_planned_qty / value_sheet,
            })

        for product in sale_line_ids.mapped('product_id'):
            bom_vals[product.id] = self._get_bom_material_total(product.bom_ids[0], {})

        material = self._get_material_product_info(bom_vals)
        return {
            'sale_line_vals_list': sale_line_vals_list,
            'bom_vals': bom_vals,
            'material_product_vals': material
        }

    def _get_bom_material_total(self, bom, vals):
        if not vals:
            vals = {}

        for line in bom.bom_line_ids:
            key = line.product_id.id
            if line.child_bom_id:
                vals = self._get_bom_material_total(line.child_bom_id, vals)
            else:
                if key in vals:
                    # 芯板材料不根据物料清单中的用量计算
                    if line.product_id.categ_id.parent_id.code and line.product_id.categ_id.parent_id.code == 'CR':
                        vals[key] += 1
                    else:
                        vals[key] += line.product_qty
                else:
                    if line.product_id.categ_id.parent_id.code and line.product_id.categ_id.parent_id.code == 'CR':
                        vals[key] = 1
                    else:
                        vals[key] = line.product_qty
        return vals

    def _get_material_product_info(self, vals):
        if not vals:
            vals = {}
        product_vals = {}
        product_list = list(set(key for value in vals.values() for key in value.keys()))
        for product in self.env['product.product'].browse(product_list):
            if (product.categ_id.code and product.categ_id.code == 'CR') or (product.categ_id.parent_id.code and product.categ_id.parent_id.code == 'CR'):
                cr = True
            else:
                cr = False
            product_vals[product.id] = {
                'product_name': product.name,
                'product_code': product.default_code,
                'product_cr': cr,
                'product_stock_qty': product.s_actual_quantity,
                'uom_name': product.uom_id.name,
                'incoming_qty': product.s_contain_incoming_qty
            }
        return product_vals
    # def get_material_consumption_total(self, sale_info=None):
    #     if not sale_info:
    #         sale_info = {}
    #     sale_line_ids = self.env['sale.order.line'].search([
    #         ('product_id.detailed_type', '=', 'product'),
    #         ('order_id.state', '=', 'sale'),
    #         ('s_remaining_planned_qty', '>', 0),
    #         ('s_plan_close', '=', False)
    #     ])
    #
    #     for line in sale_line_ids:
    #         ...

    # product_id = fields.Many2one('product.product', string='Product')
    # m_product_id = fields.Many2one('product.product', string='Material')
    # qty = fields.Float('qty')
    # uom_id = fields.Many2one('uom.uom', string='Uom')
    # stock_qty = fields.Float('Stock Quantity', digits='Product Unit of Measure', related='product_id.s_actual_quantity')
    #
    # @property
    # def _table_query(self):
    #     sql = """
    #         WITH RECURSIVE bom_tree AS (
    #             -- 基础查询：获取顶层 BOM 的子项
    #             SELECT
    #                 mb.product_tmpl_id mb_product_tmpl_id,
    #                 mbl.product_id,
    #                 mbl.product_qty AS qty,
    #                 mb.id AS top_bom_id,
    #                 mbl.product_uom_id  product_uom_id
    #             FROM mrp_bom mb
    #             LEFT JOIN mrp_bom_line mbl ON mb.id = mbl.bom_id
    #             LEFT JOIN product_product pp ON mbl.product_id = pp.id
    #             left join product_template pt on mb.product_tmpl_id = pt.id
    #             left join product_category pc on pt.categ_id = pc.id
    #             where pc.code = 'CHP'
    #
    #             UNION ALL
    #
    #             -- 递归查询：查找子 BOM 的子项
    #             SELECT
    #                 child_mb.product_tmpl_id mb_product_tmpl_id,
    #                 child_mbl.product_id,
    #                 child_mbl.product_qty AS qty,
    #                 parent.top_bom_id,
    #                 child_mbl.product_uom_id product_uom_id
    #             FROM bom_tree parent
    #                 left join product_product pp2 on parent.product_id = pp2.id
    #                 left join product_template pt2 on pp2.product_tmpl_id = pt2.id
    #                 INNER JOIN mrp_bom child_mb ON pt2.id = child_mb.product_tmpl_id
    #             INNER JOIN mrp_bom_line child_mbl ON child_mb.id = child_mbl.bom_id
    #             LEFT JOIN product_product pp3 ON child_mbl.product_id = pp3.id
    #         )
    #         SELECT
    #             row_number() OVER() AS id,
    #             sol.product_id  product_id,
    #             pp9.id m_product_id,
    #             sol.s_remaining_planned_qty * bom_tree.qty qty,
    #             bom_tree.product_uom_id uom_id
    #         from sale_order_line sol
    #         left join product_product pp8 on sol.product_id = pp8.id
    #         left join product_template pt8 on pp8.product_tmpl_id = pt8.id
    #         left join bom_tree on bom_tree.mb_product_tmpl_id = pt8.id
    #         left join product_product pp9 on bom_tree.product_id = pp9.id
    #         left join product_template pt9 on pp9.product_tmpl_id = pt9.id
    #         left join product_category pc9 on pt9.categ_id = pc9.id
    #         where pt8.detailed_type = 'product'
    #         and sol.s_remaining_planned_qty > 0
    #         and pc9.code != 'BCP'
    #     """
    #     return sql
